﻿using System;
using System.Collections.Generic;

using System.Text;
using HZ.DrugTrade.Model;
using HZ.Common;
using System.Data;
using System.Data.SqlClient;

namespace HZ.DrugTrade.DAL
{
    /// <summary>
    /// 数据访问逻辑
    /// </summary>
    public class DAL_SelectRelation
    {

        /// <summary>
        /// 是否可删除可配送（尚未开始采购）
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public static bool CheckCanDelRelation(string UserID)
        {
            string strSql = string.Format(@" SELECT * FROM View_UserRule WHERE UserID='{0}' and  OperatingRuleID=1921", UserID);

            return HZ.Data.DbHelperFactory.Create().ExecuteDataSet(strSql).Tables[0].Rows.Count > 0;
        }


        /// <summary>
        /// 条件查询
        /// </summary>
        /// <param name="where">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns></returns>
        public static IList<SelectRelation> GetSelectRelationByWhere(HZ.DrugTrade.Model.SelectRelation query, int currentPage, int pageSize, out int totalRecord)
        {
            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.SelectRelation>(query, true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT * FROM TB_SelectRelation  WHERE 1=1 {0} ", where), pageSize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.SelectRelation>(dt);
        }

        /// <summary>
        /// 获取条数
        /// </summary>
        /// <returns></returns>
        public static int GetSelectRelationCount()
        {

            string strSql = string.Format(@"select count(*)  from  TB_SelectRelation");

            return int.Parse(HZ.Data.DbHelperFactory.Create().ExecuteDataSet(strSql).Tables[0].Rows[0][0].ToString());
        }

        /// <summary>
        /// 存在性
        /// </summary>
        /// <param name="selectRelation"></param>
        /// <returns></returns>
        public static int CheckExistsSelectRelation(HZ.DrugTrade.Model.SelectRelation selectRelation)
        {
            string strSql = string.Format(@"select *  from  TB_SelectRelation
 WHERE CompanyID_PS = '{0}' and ProcureCatalogID='{1}' and AreaID='{2}'", selectRelation.CompanyID_PS, selectRelation.ProcureCatalogID, selectRelation.AreaID);

            return HZ.Data.DbHelperFactory.Create().ExecuteDataSet(strSql).Tables[0].Rows.Count;
        }
        /// <summary>
        /// 存在性
        /// </summary>
        /// <param name="selectRelation"></param>
        /// <returns></returns>
        public static int CheckExistsSelectRelation2(HZ.DrugTrade.Model.SelectRelation selectRelation)
        {
            string strSql = string.Format(@"select *  from  TB_SelectRelation
 WHERE ProcureCatalogID='{0}' and AreaID='{1}'", selectRelation.ProcureCatalogID, selectRelation.AreaID);
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteDataSet(strSql).Tables[0];
            int _length = dt.Rows.Count;
            if (_length <= 0)
            {
                return 0;
            }
            for (int _index = 0; _index < _length; _index++)
            {
                if (selectRelation.CompanyID_PS.Equals(dt.Rows[_index]["CompanyID_PS"].ToString()))
                {
                    return 2;
                }
            }
            if (_length >= 5)
            {
                return 5;
            }

            return 0;
        }
        /// <summary>
        /// 增加
        /// </summary>
        /// <param name="SelectRelation"></param>
        /// <returns></returns>
        public static int AddSelectRelation(HZ.DrugTrade.Model.SelectRelation selectRelation,string ProjectID)
        {
            string sql = "";
            if (ProjectID == "0001")
            {
                sql = string.Format(@"select count(*) from tb_psselectarea where companyps_id = '{0}' and areaid = '{1}' and ishcygcg is null", selectRelation.CompanyID_PS, selectRelation.AreaID);
            }
            else
            {
                sql = string.Format(@"select count(*) from tb_psselectarea where companyps_id = '{0}' and areaid = '{1}' and ishcygcg = 1", selectRelation.CompanyID_PS, selectRelation.AreaID);
            }
            int count = Convert.ToInt32(HZ.Data.DbHelperFactory.Create().ExecuteScalar(sql));
            if (count > 0)
            {
                int result = CheckExistsSelectRelation2(selectRelation);
                if (result > 0 && ProjectID == "0001")
                {
                    return result;
                }
                else if (result > 0 && result != 5 && ProjectID == "0002")
                {
                    return result;
                }
                else
                {
                    //当前状态初始化为-1 标识未提交   
                    string strSql = string.Format(@"INSERT INTO TB_SelectRelation
           (ProcureCatalogID
           ,AreaID
           ,CompanyID_TB
           ,CompanyID_PS
           ,isMain
           ,Status)
     VALUES ('{0}','{1}' ,'{2}','{3}',1,{4})", selectRelation.ProcureCatalogID, selectRelation.AreaID, selectRelation.CompanyID_TB, selectRelation.CompanyID_PS, ProjectID == "0002" ? "0" : "-1");

                    return HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(strSql);
                }
            }
            else
            {
                return 6;
            }
        }

        /// <summary>
        /// 批量增加
        /// </summary>
        /// <param name="lst"></param>
        /// <returns></returns>
        public static int AddSelectRelation(IList<Model.SelectRelation> lst, object ishcygcg, out int[] arrayResult)
        {
            var h = HZ.Data.DbHelperFactory.Create();

            int result = 0;
            string strIshcygcg = string.Empty;
            if (ishcygcg != null)
            {
                if (ishcygcg != "")
                    strIshcygcg = ishcygcg.ToString();
            }
            arrayResult = new int[5] { 0, 0, 0, 0, 0 };
            try
            {
                h.BeginTrans();

                arrayResult[0] = lst.Count;

                foreach (Model.SelectRelation selectRelation in lst)
                {
                    string strSql = string.Empty;
                    #region 检查是否能添加
                    result = 0;
                    strSql = string.Format(@"select *  from  TB_SelectRelation
 WHERE ProcureCatalogID='{0}' and AreaID='{1}'", selectRelation.ProcureCatalogID, selectRelation.AreaID);
                    DataTable dt = h.ExecuteDataSet(strSql).Tables[0];
                    int _length = dt.Rows.Count;
                    if (_length <= 0)
                    {
                        result = 0;
                    }
                    for (int _index = 0; _index < _length; _index++)
                    {
                        if (selectRelation.CompanyID_PS.Equals(dt.Rows[_index]["CompanyID_PS"].ToString()))
                        {
                            result = 2;
                        }
                    }
                    if (_length >= 5 && result != 2)
                    {
                        result = 5;
                    }
                    #endregion
                    if (result == 0 || (result== 5 && !string.IsNullOrEmpty(strIshcygcg)))
                    {
                        #region 添加
                        //当前状态初始化为-1 标识未提交
                        strSql = string.Format(@"INSERT INTO TB_SelectRelation
           (ProcureCatalogID
           ,AreaID
           ,CompanyID_TB
           ,CompanyID_PS
           ,isMain
           ,Status)
     VALUES ('{0}','{1}' ,'{2}','{3}',1,0)", selectRelation.ProcureCatalogID, selectRelation.AreaID, selectRelation.CompanyID_TB, selectRelation.CompanyID_PS);

                        result = h.ExecuteNonQuery(strSql);

                        #endregion

                        if (result == 1)
                        {
                            //添加成功
                            arrayResult[1]++;
                        }
                        else
                        {
                            //添加失败
                            arrayResult[2]++;
                        }
                    }
                    else if (result == 2)
                    {
                        //已存在
                        arrayResult[3]++;
                    }
                    else if (result == 5 && string.IsNullOrEmpty(strIshcygcg)) //string.IsNullOrEmpty(strIshcygcg) 低值耗材
                    {
                        //超过五家不能添加
                        arrayResult[4]++;
                    }
                }

                if (arrayResult[0] == arrayResult[1] + arrayResult[3] + arrayResult[4])
                {

                    h.CommitTrans();
                    return 1;
                }
                else
                {
                    throw new Exception("存在失败的！");
                }
            }
            catch (Exception ee)
            {
                h.RollTrans();
                HZ.Common.log4netManager.LogDebug("DAL_AreaDistribution", ee.Message, ee);
                return 0;
            }

            //            int result = CheckExistsSelectRelation2(selectRelation);
            //            if (result > 0)
            //            {
            //                return result;
            //            }
            //            else
            //            {
            //                //当前状态初始化为-1 标识未提交
            //                string strSql = string.Format(@"INSERT INTO TB_SelectRelation
            //           ([ProcureCatalogID]
            //           ,AreaID
            //           ,[CompanyID_TB]
            //           ,CompanyID_PS
            //           ,[isMain]
            //           ,[Status])
            //     VALUES ('{0}','{1}' ,'{2}','{3}',1,-1)", selectRelation.ProcureCatalogID, selectRelation.AreaID, selectRelation.CompanyID_TB, selectRelation.CompanyID_PS);

            //                return HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(strSql);

            //}

        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="selectRelation"></param>
        /// <returns></returns>
        public static int UpdateSelectRelation(HZ.DrugTrade.Model.SelectRelation selectRelation)
        {
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                string strSql = "";
                string strsql1 = "";
//                string strSql = string.Format(@"UPDATE TB_SelectRelation
//   SET Status ={0} 
// WHERE CompanyID_PS = '{1}' and ProcureCatalogID='{2}' and AreaID='{3}' and Status<>3;", selectRelation.Status, selectRelation.CompanyID_PS, selectRelation.ProcureCatalogID, selectRelation.AreaID);
                if (selectRelation.Status == 1)
                {
                    string projectID = HZ.Data.DbHelperFactory.Create().ExecuteScalar(string.Format("select projectid from view_procurecatalog_tb where procurecatalogid = '{0}'", selectRelation.ProcureCatalogID)).ToString();
                    if (projectID == "0001") //低值耗材-只更新状态为配送企业同意状态
                    {
                        //                    DataTable dt = h.ExecuteTable(string.Format(@"select * from TB_AreaDistribution where CompanyID_Main = '{3}' and CompanyID_PS = '{0}' and AreaID='{2}' and ProcureCatalogID='{1}'", selectRelation.CompanyID_PS, selectRelation.ProcureCatalogID, selectRelation.AreaID, selectRelation.CompanyID_TB));
                        //                    if (dt.Rows.Count == 0)
                        //                    {
                        //                        //modfiy by ysm 20130322 配送企业同意后直接将状态更新为可配送状态
                        //                        strSql = string.Format(@"INSERT INTO TB_AreaDistribution
                        //                           (ProcureCatalogID
                        //                           ,AreaID
                        //                           ,CompanyID_Main
                        //                           ,CompanyID_PS ) VALUES ('{1}','{2}','{3}','{0}')", selectRelation.CompanyID_PS, selectRelation.ProcureCatalogID, selectRelation.AreaID, selectRelation.CompanyID_TB);
                        //                        h.ExecuteNonQuery(strSql);
                        //                    }
                        strsql1 = string.Format(@"UPDATE TB_SelectRelation
   SET Status =1
 WHERE CompanyID_TB = '{3}' and CompanyID_PS = '{0}' and AreaID='{2}' and ProcureCatalogID='{1}' and Status=0", selectRelation.CompanyID_PS, selectRelation.ProcureCatalogID, selectRelation.AreaID, selectRelation.CompanyID_TB);
                    }
                    else //高值耗材--配送企业同意后直接将状态更新为可配送状态，不需生产企业提交
                    {
                        DataTable dt = h.ExecuteTable(string.Format(@"select * from TB_AreaDistribution where CompanyID_Main = '{3}' and CompanyID_PS = '{0}' and AreaID='{2}' and ProcureCatalogID='{1}'", selectRelation.CompanyID_PS, selectRelation.ProcureCatalogID, selectRelation.AreaID, selectRelation.CompanyID_TB));
                        if (dt.Rows.Count == 0)
                        {
                            //modfiy by ysm 20130322 配送企业同意后直接将状态更新为可配送状态
                            strSql = string.Format(@"INSERT INTO TB_AreaDistribution
                                                   (ProcureCatalogID
                                                   ,AreaID
                                                   ,CompanyID_Main
                                                   ,CompanyID_PS ) VALUES ('{1}','{2}','{3}','{0}')", selectRelation.CompanyID_PS, selectRelation.ProcureCatalogID, selectRelation.AreaID, selectRelation.CompanyID_TB);
                            h.ExecuteNonQuery(strSql);
                        }
                        strsql1 = string.Format(@"UPDATE TB_SelectRelation
   SET Status =3
 WHERE CompanyID_TB = '{3}' and CompanyID_PS = '{0}' and AreaID='{2}' and ProcureCatalogID='{1}' and Status=0", selectRelation.CompanyID_PS, selectRelation.ProcureCatalogID, selectRelation.AreaID, selectRelation.CompanyID_TB);
                    }
                }
                else
                {
                    strSql = string.Format(@"DELETE FROM TB_AreaDistribution
                 WHERE CompanyID_PS = '{0}' and ProcureCatalogID='{1}' and AreaID='{2}'", selectRelation.CompanyID_PS, selectRelation.ProcureCatalogID, selectRelation.AreaID, selectRelation.Status);
                    h.ExecuteNonQuery(strSql);
                    strsql1 = string.Format(@"UPDATE TB_SelectRelation
   SET Status ={3} 
 WHERE CompanyID_PS = '{0}' and ProcureCatalogID='{1}' and AreaID='{2}' and Status=0", selectRelation.CompanyID_PS, selectRelation.ProcureCatalogID, selectRelation.AreaID, selectRelation.Status);
                }

                
               int i= h.ExecuteNonQuery(strsql1);

                h.CommitTrans();
                return i;
            }
            catch (Exception ee)
            {
                h.RollTrans();
                HZ.Common.log4netManager.LogDebug("DAL_AreaDistribution", ee.Message, ee);
                return 0;
            }
        }
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="selectRelation"></param>
        /// <returns></returns>
        public static int UpdateSelectRelation(IList<Model.SelectRelation> lst)
        {
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                string strSql = string.Empty;
                foreach (Model.SelectRelation selectRelation in lst)
                {
                    string projectID = HZ.Data.DbHelperFactory.Create().ExecuteScalar(string.Format("select projectid from view_procurecatalog_tb where procurecatalogid = '{0}'", selectRelation.ProcureCatalogID)).ToString();
                    if (projectID == "0001") //低值耗材-只更新状态为配送企业同意状态
                    {
                        strSql = string.Format(@" UPDATE TB_SelectRelation
   SET Status ={0} 
 WHERE CompanyID_PS = '{1}' and ProcureCatalogID='{2}' and AreaID='{3}' and Status<>3", selectRelation.Status, selectRelation.CompanyID_PS, selectRelation.ProcureCatalogID, selectRelation.AreaID);
                    }
                    else //高值耗材--配送企业同意后直接将状态更新为可配送状态，不需生产企业提交
                    {
                        if (selectRelation.Status == 1)
                        {
                            DataTable dt = h.ExecuteTable(string.Format(@"select * from TB_AreaDistribution where CompanyID_Main = '{3}' and CompanyID_PS = '{0}' and AreaID='{2}' and ProcureCatalogID='{1}'", selectRelation.CompanyID_PS, selectRelation.ProcureCatalogID, selectRelation.AreaID, selectRelation.CompanyID_TB));
                            if (dt.Rows.Count == 0)
                            {
                                //modfiy by ysm 20130322 配送企业同意后直接将状态更新为可配送状态
                                strSql = string.Format(@"INSERT INTO TB_AreaDistribution
                                                   (ProcureCatalogID
                                                   ,AreaID
                                                   ,CompanyID_Main
                                                   ,CompanyID_PS ) VALUES ('{1}','{2}','{3}','{0}')", selectRelation.CompanyID_PS, selectRelation.ProcureCatalogID, selectRelation.AreaID, selectRelation.CompanyID_TB);
                                h.ExecuteNonQuery(strSql);
                            }
                            strSql = string.Format(@"UPDATE TB_SelectRelation
   SET Status =3
 WHERE CompanyID_TB = '{3}' and CompanyID_PS = '{0}' and AreaID='{2}' and ProcureCatalogID='{1}'", selectRelation.CompanyID_PS, selectRelation.ProcureCatalogID, selectRelation.AreaID, selectRelation.CompanyID_TB);
                        }
                        else
                        {
                            strSql = string.Format(@" UPDATE TB_SelectRelation
   SET Status ={0} 
 WHERE CompanyID_PS = '{1}' and ProcureCatalogID='{2}' and AreaID='{3}' and Status<>3", selectRelation.Status, selectRelation.CompanyID_PS, selectRelation.ProcureCatalogID, selectRelation.AreaID);
                        }
                    }
                    h.ExecuteNonQuery(strSql);
                }

                h.CommitTrans();
                return 1;
            }
            catch (Exception ee)
            {
                h.RollTrans();
                HZ.Common.log4netManager.LogDebug("DAL_AreaDistribution", ee.Message, ee);
                return 0;
            }
        }
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="strCompanyID_TB"></param>
        /// <returns></returns>
        public static int UpdateSelectRelation(string strCompanyID_TB)
        {
            HZ.Data.OracleHelper h = new HZ.Data.OracleHelper();
            try
            {
                h.BeginTrans();
                string strSql = string.Format(@"UPDATE TB_SelectRelation
   SET Status =0 
 WHERE CompanyID_TB = '{0}' and  Status =-1", strCompanyID_TB);

                h.ExecuteNonQuery(strSql);

                h.CommitTrans();
                return 1;
            }
            catch (Exception ee)
            {
                h.RollTrans();
                HZ.Common.log4netManager.LogDebug("DAL_AreaDistribution", ee.Message, ee);
                return 0;
            }
        }

        /// <summary>
        /// 更新:更新可配送状态
        /// </summary>
        /// <param name="strCompanyID_TB"></param>
        /// <returns></returns>
        public static int UpdateSelectRelation(string strCompanyID_TB, int status)
        {
            var h = HZ.Data.DbHelperFactory.Create();
            int Flag = 0;//返回值
            try
            {
                h.BeginTrans();
                DataTable dt = h.ExecuteTable(string.Format(@"SELECT ProcureCatalogID,AreaID,CompanyID_TB,CompanyID_PS
                                                  FROM TB_SelectRelation WHERE CompanyID_TB = '{0}' and  Status =1 ", strCompanyID_TB));
                DataTable dt1 = h.ExecuteTable(string.Format(@"select * from TB_SelectRelation  WHERE CompanyID_TB = '{0}' and  Status =-1", strCompanyID_TB));
                DataTable dt2 = h.ExecuteTable(string.Format(@"select * from TB_SelectRelation  WHERE CompanyID_TB = '{0}' and  Status =0", strCompanyID_TB));
                if (dt.Rows.Count > 0)
                {
                    string strSql = string.Format(@"BEGIN INSERT INTO TB_AreaDistribution
                                             (ProcureCatalogID,AreaID,CompanyID_Main,CompanyID_PS )
                                    SELECT ProcureCatalogID,AreaID,CompanyID_TB,CompanyID_PS
                                      FROM TB_SelectRelation
                                     WHERE CompanyID_TB = '{0}' and  Status =1;UPDATE TB_SelectRelation
   SET Status ={1}
 WHERE CompanyID_TB = '{0}' and  Status =1; END; ", strCompanyID_TB,status);

                    h.ExecuteNonQuery(strSql);

//                    strSql = string.Format(@"UPDATE TB_SelectRelation
//   SET Status ={1}
// WHERE CompanyID_TB = '{0}' and  Status =1 ", strCompanyID_TB, status);

//                    h.ExecuteNonQuery(strSql);

                    h.CommitTrans();
                    Flag = 1;
                }
                if (dt1.Rows.Count > 0 && dt2.Rows.Count > 0)//有未提交和配送企业未确认品规
                {
                    Flag = 2;
                }
                if (dt1.Rows.Count > 0 && dt2.Rows.Count <= 0)//有未提交品规
                {
                    Flag = 3;
                }
                if (dt1.Rows.Count <= 0 && dt2.Rows.Count > 0)//有配送企业未确认品规
                {
                    Flag = 4;
                }
            }
            catch (Exception ee)
            {
                h.RollTrans();
                HZ.Common.log4netManager.LogDebug("DAL_AreaDistribution", ee.Message, ee);
                return 0;
            }
            return Flag;
        }
        /// <summary>
        /// 删除企业的配送关系
        /// </summary>
        /// <param name="selectRelation">配送关系模型</param>
        /// <returns>返回值 0：删除出错 1 删除成功 2 医疗机构已经选择</returns>
        public static int DeleteSelectRelation(HZ.DrugTrade.Model.SelectRelation selectRelation)
        {
            //            string strSql = string.Format(@"delete  TB_SelectRelation
            // WHERE CompanyID_PS = '{0}' and ProcureCatalogID='{1}' and AreaID='{2}'  and Status<>3", selectRelation.CompanyID_PS, selectRelation.ProcureCatalogID, selectRelation.AreaID);

            //            return HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(strSql);

            //wgh 2010-7-12 
            //企业能删除医疗机构选择了配送之外的所有选择的配送
            int intReturn = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            StringBuilder sb = new StringBuilder();
            try
            {
                h.BeginTrans();
                //如果配送企业被医疗机构选择，则不能进行删除操作
                //                sb.Append(@"
                //                            IF Exists(Select  top 1 * From TB_HospitalProcureCataLog where ProcureCatalogID=@in_ProcureCatalogID
                //                                                        And CompanyID_PS=@in_CompanyID_PS
                //                                                        and Exists (Select * From TB_Hospital Where TB_HospitalProcureCataLog.HospitalID=TB_Hospital.hospitalID
                //                                                        And areaid like substring(@in_AreaID,1,4)+'%'))
                //                                    Select 2
                //                                 Else
                //                                 begin
                //                                     Delete From TB_SelectRelation
                //                                     Where  ProcureCatalogID=@in_ProcureCatalogID And CompanyID_PS=@in_CompanyID_PS And AreaID=@in_AreaID
                //                                     Delete From TB_AreaDistribution
                //                                     Where ProcureCatalogID=@in_ProcureCatalogID And CompanyID_PS=@in_CompanyID_PS And  AreaID=@in_AreaID ");
                //                if (selectRelation.Status == 3)             //只有删除可配送状态才记录日志
                //                {
                //                    sb.Append(@" INSERT INTO TB_AreaDistributionDelLog
                //                                                                   (ProcureCataLogID
                //                                                                    ,AreaID
                //                                                                   ,AreaName
                //                                                                   ,CompanyID_TB
                //                                                                   ,CompanyID_PS
                //                                                                   ,OperatedTime)
                //                                                           Select @in_ProcureCatalogID
                //                                                                 ,AreaID
                //                                                                 ,AreaName 
                //                                                                 ,@in_Operator
                //                                                                 ,@in_CompanyID_PS
                //                                                                 ,sysdate
                //                                                                  From TB_Area Where AreaID=@in_AreaID");
                //                }


                //                sb.Append("   Select 1  End  ");
                //                IDataParameter[] paras = new IDataParameter[]{
                //                   new SqlParameter("@in_CompanyID_PS",selectRelation.CompanyID_PS),
                //                   new SqlParameter("@in_Operator",selectRelation.CompanyID_TB),
                //                   new SqlParameter("@in_ProcureCatalogID",selectRelation.ProcureCatalogID),
                //                   new SqlParameter("@in_AreaID",selectRelation.AreaID)
                //                  };
                //                intReturn = (int)db.ExecuteScalar(CommandType.Text, sb.ToString(), paras);//返回值 返回2 医疗机构已经选择 返回1 删除成功


                //Updated by sinodier 2011年1月13日1:09:08

               
                //                  //如果配送企业被医疗机构选择，则不能进行删除操作
                //                sb.Append(@"
                //                            IF Exists(Select  top 1 * From TB_HospitalProcureCataLog where ProcureCatalogID=@in_ProcureCatalogID
                //                                                        And CompanyID_PS=@in_CompanyID_PS
                //                                                        and Exists (Select * From TB_Hospital Where TB_HospitalProcureCataLog.HospitalID=TB_Hospital.hospitalID
                //                                                        And areaid like substring(@in_AreaID,1,4)+'%'))
                //                                    Select 2
                //                                 Else
                //                                 begin
                //                                     Delete From TB_SelectRelation
                //                                     Where  ProcureCatalogID=@in_ProcureCatalogID And CompanyID_PS=@in_CompanyID_PS And AreaID=@in_AreaID
                //                                     Delete From TB_AreaDistribution
                //                                     Where ProcureCatalogID=@in_ProcureCatalogID And CompanyID_PS=@in_CompanyID_PS And  AreaID=@in_AreaID ");
                //                if (selectRelation.Status == 3)             //只有删除可配送状态才记录日志
                //                {
                //                    sb.Append(@" INSERT INTO TB_AreaDistributionDelLog
                //                                                                   (ProcureCataLogID
                //                                                                    ,AreaID
                //                                                                   ,AreaName
                //                                                                   ,CompanyID_TB
                //                                                                   ,CompanyID_PS
                //                                                                   ,OperatedTime)
                //                                                           Select @in_ProcureCatalogID
                //                                                                 ,AreaID
                //                                                                 ,AreaName 
                //                                                                 ,@in_Operator
                //                                                                 ,@in_CompanyID_PS
                //                                                                 ,sysdate
                //                                                                  From TB_Area Where AreaID=@in_AreaID");
                //                }


                //                sb.Append("   Select 1  End  ");
                //                IDataParameter[] paras = new IDataParameter[]{
                //                   new SqlParameter("@in_CompanyID_PS",selectRelation.CompanyID_PS),
                //                   new SqlParameter("@in_Operator",selectRelation.CompanyID_TB),
                //                   new SqlParameter("@in_ProcureCatalogID",selectRelation.ProcureCatalogID),
                //                   new SqlParameter("@in_AreaID",selectRelation.AreaID)
                //                  };
                // intReturn = (int)db.ExecuteScalar(CommandType.Text, sb.ToString(), paras);//返回值 返回2 医疗机构已经选择 返回1 删除成功
                #region Updated by sinodier 2011年1月13日1:09:08
               


                /*
                 * 01配送企业被医疗机构选择，不删除
                 * 02配送企业未被医疗机构选择，删除：TB_SelectRelation,TB_AreaDistribution
                 * 03删除可配送状态的需记录日志
                 
                 */
                #region 01配送企业被医疗机构选择，不删除

                string strCheck = string.Format(@"Select count(1) num From view_hospitalprocurecatalog  
                                     where rownum<=1 and ProcureCatalogID=@in_ProcureCatalogID
                                     And CompanyID_PS=@in_CompanyID_PS
                                     and areaid like '{0}%'", selectRelation.AreaID.Substring(0,4));

                List<string> parasnames1 = new List<string>();
                List<object> values1 = new List<object>();

                parasnames1.Add("@in_ProcureCatalogID");
                values1.Add(selectRelation.ProcureCatalogID);

                parasnames1.Add("@in_CompanyID_PS");
                values1.Add(selectRelation.CompanyID_PS);

              

                DataTable dt = h.ExecuteTable(CommandType.Text, strCheck, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames1, values1).ToArray());

                int num = int.Parse(dt.Rows[0]["num"].ToString());

                #endregion

                if (num == 0)
                {
                    parasnames1.Add("@in_AreaID");
                    values1.Add(selectRelation.AreaID);

                    #region 02配送企业未被医疗机构选择，删除：TB_SelectRelation,TB_AreaDistribution

                    string strDelSelectRelation = @" Delete From TB_SelectRelation
                                     Where  ProcureCatalogID=@in_ProcureCatalogID And CompanyID_PS=@in_CompanyID_PS And AreaID=@in_AreaID";

                    h.ExecuteNonQuery(CommandType.Text, strDelSelectRelation, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames1, values1).ToArray());


                    string strDelAreaDistribution = @"Delete From TB_AreaDistribution
                                     Where ProcureCatalogID=@in_ProcureCatalogID And CompanyID_PS=@in_CompanyID_PS And  AreaID=@in_AreaID";

                    h.ExecuteNonQuery(CommandType.Text, strDelAreaDistribution, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames1, values1).ToArray());

                    #endregion

                    #region 03删除可配送状态的需记录日志

                    if (selectRelation.Status == 3)             //只有删除可配送状态才记录日志
                    {
                        string strInsertAreaDistributionDelLog = @"INSERT INTO TB_AreaDistributionDelLog
                                                                   (ProcureCataLogID
                                                                    ,AreaID
                                                                   ,AreaName
                                                                   ,CompanyID_TB
                                                                   ,CompanyID_PS
                                                                   ,OperatedTime)
                                                           Select @in_ProcureCatalogID
                                                                 ,AreaID
                                                                 ,AreaName 
                                                                 ,@in_Operator
                                                                 ,@in_CompanyID_PS
                                                                 ,sysdate
                                                                  From TB_Area Where AreaID=@in_AreaID";

                        List<string> parasnames2 = new List<string>();
                        List<object> values2 = new List<object>();

                        parasnames2.Add("@in_ProcureCatalogID");
                        values2.Add(selectRelation.ProcureCatalogID);

                        parasnames2.Add("@in_CompanyID_PS");
                        values2.Add(selectRelation.CompanyID_PS);

                        parasnames2.Add("@in_AreaID");
                        values2.Add(selectRelation.AreaID);

                        parasnames2.Add("@in_Operator");
                        values2.Add(selectRelation.CompanyID_TB);

                        h.ExecuteNonQuery(CommandType.Text, strInsertAreaDistributionDelLog, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames2, values2).ToArray());


                    }
                    #endregion
                    intReturn = 1;
                }
                else
                {
                    intReturn = 2;
                }
                #endregion

                h.CommitTrans();

            }
            catch (Exception ee)
            {

                HZ.Common.log4netManager.LogDebug(sb.ToString(), ee.Message, ee);
                h.RollTrans();
                intReturn = 0;
                throw;
            }
            return intReturn;
        }

        /// <summary>
        /// 统计
        /// </summary>
        /// <returns></returns>
        public static DataTable GetAreaDistribution()
        {
            string strSql = @"SELECT 'relation' t,count(*)  c
                                              FROM TB_AreaDistribution
                                            union 
                                            SELECT 'tb'  t,count (distinct companyid_main)  c
                                              FROM TB_AreaDistribution
                                            union
                                            SELECT 'ps'  t,count (distinct companyid_ps)  c
                                              FROM TB_AreaDistribution ";

            return HZ.Data.DbHelperFactory.Create().ExecuteDataSet(strSql).Tables[0];
        }
        //        /// <summary>
        //        /// 
        //        /// </summary>
        //        /// <param name="selectRelation"></param>
        //        /// <returns></returns>
        //        public static int DeleteSelectRelationByAdmin(HZ.DrugTrade.Model.SelectRelation selectRelation)
        //        {
        //            HZ.Data.SQLHelper h = new HZ.Data.SQLHelper();
        //            try
        //            {
        //                h.BeginTrans();


        //                string strSql = string.Format(@"delete  TB_SelectRelation
        // WHERE CompanyID_PS = '{0}' and ProcureCatalogID='{1}' and AreaID='{2}'", selectRelation.CompanyID_PS, selectRelation.ProcureCatalogID, selectRelation.AreaID);

        //                h.ExecuteNonQuery(strSql);

        //                strSql = string.Format(@"DELETE FROM [TB_AreaDistribution]
        // WHERE CompanyID_PS = '{0}' and ProcureCatalogID='{1}' and AreaID='{2}';", selectRelation.CompanyID_PS, selectRelation.ProcureCatalogID, selectRelation.AreaID);

        //                h.ExecuteNonQuery(strSql);

        //                h.CommitTrans();
        //                return 1;
        //            }
        //            catch (Exception ee)
        //            {
        //                h.RollTrans();
        //                HZ.Common.log4netManager.LogDebug("DeleteSelectRelationByAdmin", ee.Message, ee);
        //                return 0;
        //            }
        //        }


        /// <summary>
        /// 统计
        /// </summary>
        /// <returns></returns>
        public static DataTable GetEspeciallyRelationShip()
        {
            string strSql = string.Format(@"SELECT ProcureCatalogID
      ,AreaID
      ,TB_SelectRelation.CompanyID_TB as CompanyID_TB_false
      ,CompanyID_PS
      ,isMain
      ,Status
      ,Operatetime,tb_goods.CompanyID_TB as CompanyID_TB_true,ProductName,Outlookc,Factor,Unit
  FROM TB_SelectRelation inner join tb_goods on TB_SelectRelation.ProcureCatalogID=tb_goods.GoodsID
and TB_SelectRelation.CompanyID_TB<>tb_goods.CompanyID_TB ");

            return HZ.Data.DbHelperFactory.Create().ExecuteDataSet(strSql).Tables[0];
        }

        /// <summary>
        /// 更新:校正查因COOKIE导致的异常数据
        /// </summary>
        /// <returns></returns>
        public static int UpdateEspeciallyRelationShip()
        {
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();

//                string strSql = string.Format(@"update TB_SelectRelation set TB_SelectRelation.CompanyID_TB=tb_goods.CompanyID_TB from tb_goods where  TB_SelectRelation.ProcureCatalogID=tb_goods.GoodsID
//and TB_SelectRelation.CompanyID_TB<>tb_goods.CompanyID_TB;");

                string strSql = string.Format(@"update TB_SelectRelation A set CompanyID_TB=(SELECT B.CompanyID_TB 
                                                from tb_goods B where  A.ProcureCatalogID=B.GoodsID
                                                and A.CompanyID_TB<>B.CompanyID_TB) WHERE EXISTS(
                                                SELECT B.CompanyID_TB 
                                                from tb_goods B where  A.ProcureCatalogID=B.GoodsID
                                                and A.CompanyID_TB<>B.CompanyID_TB
                                                )");

                h.ExecuteNonQuery(strSql);

//                strSql = string.Format(@"update TB_AreaDistribution set TB_AreaDistribution.CompanyID_Main=tb_goods.CompanyID_TB from tb_goods where  TB_AreaDistribution.ProcureCatalogID=tb_goods.GoodsID
//and TB_AreaDistribution.CompanyID_Main<>tb_goods.CompanyID_TB;");

                strSql = string.Format(@"update TB_AreaDistribution A set CompanyID_Main=(SELECT B.CompanyID_TB from tb_goods B
                                         where  A.ProcureCatalogID=B.GoodsID
                                        and A.CompanyID_Main<>B.CompanyID_TB)
                                        WHERE EXISTS(
                                        SELECT B.CompanyID_TB from tb_goods B
                                         where  A.ProcureCatalogID=B.GoodsID
                                        and A.CompanyID_Main<>B.CompanyID_TB
                                        )");

                h.ExecuteNonQuery(strSql);

                h.CommitTrans();
                return 1;
            }
            catch (Exception ee)
            {
                h.RollTrans();
                HZ.Common.log4netManager.LogDebug("DAL_AreaDistribution--UpdateEspeciallyRelationShip", ee.Message, ee);
                return 0;
            }
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="selectRelation"></param>
        /// <returns></returns>
        public static int UpdateSelectRelationAuditStatus(IList<Model.SelectRelation> lst)
        {
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                string strSql = string.Empty;
                foreach (Model.SelectRelation selectRelation in lst)
                {
                    strSql = string.Format(@"begin UPDATE TB_SelectRelation
   SET AuditStatus ={0} ,AuditReason='{4}',Auditer='{5}'
 WHERE CompanyID_PS = '{1}' and ProcureCatalogID='{2}' and AreaID='{3}' and Status=3;
    update TB_AreaDistribution set Unioncitystate = {0} where CompanyID_PS = '{1}' and ProcureCatalogID='{2}' and AreaID='{3}';end;", selectRelation.AuditStatus, selectRelation.CompanyID_PS, selectRelation.ProcureCatalogID, selectRelation.AreaID, selectRelation.AuditReason, selectRelation.Auditer);

                    h.ExecuteNonQuery(strSql);
                }

                h.CommitTrans();
                return 1;
            }
            catch (Exception ee)
            {
                h.RollTrans();
                HZ.Common.log4netManager.LogDebug("DAL_AreaDistribution", ee.Message, ee);
                return 0;
            }
        }
        /// <summary>
        /// 据配送企业得到生产企业的id
        /// </summary>
        /// <param name="cmpPSId"></param>
        /// <returns></returns>
        public static string GetCmpSCByCmpPSId(string cmpPSId)
        {
            string CmpSCIDs = "";
            string sql = "select distinct(companyid_tb) from Tb_Selectrelation where companyid_ps='"+cmpPSId+"'";
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteDataSet(sql).Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                CmpSCIDs +="'"+ dr[0].ToString()+"',";
            }
            return CmpSCIDs;
        }
        /// <summary>
        /// 企业提交申请删除配送关系
        /// add by Meng 2013-04-10
        /// </summary>
        /// <param name="srelation"></param>
        /// <returns></returns>
        public static int GetDelRequestDelete(HZ.DrugTrade.Model.SelectRelation srelation)
        {
            string strsql = string.Format(@"update tb_selectrelation set RequestDelete=0 where procurecatalogid = '{0}' and areaid = '{1}' and companyid_ps = '{2}'",srelation.ProcureCatalogID,srelation.AreaID,srelation.CompanyID_PS);
            return HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(strsql);
        }
    }
}
